内部表外部表分区表

其他操作

设置参数

启动Hive时，可以在命令行添加 -hiveconf param=value 来设定参数，这些设定仅对本次启动有效。

# 启动时指定参数
hive -hiveconf hive.exec.mode.local.auto=true
# 在命令行检查参数是否生效
hive> set hive.exec.mode.local.auto;
hive.exec.mode.local.auto=true

可在 Hive 命令行中使用SET关键字设定参数，同样仅对本次启动有效

hive> set hive.exec.mode.local.auto=false;
hive> set hive.exec.mode.local.auto;
hive.exec.mode.local.auto=false

外部执行命令

hive -e "select * from users"

执行脚本中sql语句

# 创建文件hqlfile1.sql，内容：select * from users
# 执行文件中的SQL语句
hive -f hqlfile1.sql
# 执行文件中的SQL语句，将结果写入文件
hive -f hqlfile1.sql >> result1.log

查看数据库

describe database extended default;

删除数据库

-- 删除一个空数据库
drop database databasename;
-- 如果数据库不为空，使用 cascade 强制删除
drop database databasename cascade;

数据准备

mkdir /home/bigdata/data
vi /home/bigdata/data/t1.dat

2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

内部表

-- 创建内部表
create table t1(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
-- 显示表的定义，显示的信息较少
desc t1;
-- 显示表的定义，显示的信息多，格式友好
desc formatted t1;
-- 加载数据
load data local inpath '/home/bigdata/data/t1.dat' into table t1;
-- 查询数据
select * from t1;
-- 查询数据文件
dfs -ls /user/hive/warehouse/t1;
-- 删除表。表和数据同时被删除
drop table t1;
-- 再次查询数据文件，已经被删除

外部表

-- 创建外部表
create external table t2(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 显示表的定义
desc formatted t2;
-- 加载数据
load data local inpath '/home/bigdata/data/t1.dat' into table
t2;
-- 查询数据
select * from t2;
-- 删除表。表删除了，目录仍然存在
drop table t2;
-- 再次查询数据文件，仍然存在

内部表与外部表的转换

-- 创建内部表，加载数据，并检查数据文件和表的定义
create table t1(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
load data local inpath '/home/bigdata/data/t1.dat' into table t1;
dfs -ls /user/hive/warehouse/t1;
desc formatted t1;

-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查询表信息，是否转换成功
desc formatted t1;
-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');
-- 查询表信息，是否转换成功
desc formatted t1;

drop table t1;

create table t1(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

select * from t1;

分区表

Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。而有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据 时，可避免全表扫描，提高查询效率。

基本使用

-- 创建表
create table if not exists t3(
id int
,name string
,hobby array<string>
,addr map<String,string>
)
partitioned by (dt string)
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':';


-- 加载数据
load data local inpath "/home/bigdata/data/t1.dat" into table t3 partition(dt="2020-06-01");
load data local inpath "/home/bigdata/data/t1.dat" into table t3 partition(dt="2020-06-02");

select * from t3 where dt='2020-06-01';
select * from t3;

查看分区

show partitions t3;

新增分区

-- 增加一个分区，不加载数据
alter table t3 add partition(dt='2020-06-03');
-- 增加多个分区，不加载数据
alter table t3 add partition(dt='2020-06-05') partition(dt='2020-06-06');
-- 增加多个分区。准备数据
hdfs dfs -cp /user/hive/warehouse/t3/dt=2020-06-01 /user/hive/warehouse/t3/dt=2020-06-07
hdfs dfs -cp /user/hive/warehouse/t3/dt=2020-06-01 /user/hive/warehouse/t3/dt=2020-06-08
-- 增加多个分区。加载数据
alter table t3 add partition(dt='2020-06-07') location '/user/hive/warehouse/t3/dt=2020-06-07' partition(dt='2020-06-08') location '/user/hive/warehouse/t3/dt=2020-06-08';
-- 查询数据
select * from t3;

修改分区数据路径

alter table t3 partition(dt='2020-06-01') set location '/user/hive/warehouse/t3/dt=2020-06-07';

删除分区

-- 可以删除一个或多个分区，用逗号隔开
alter table t3 drop partition(dt='2020-06-03'),partition(dt='2020-06-04');

分桶表

当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶技术将数据划分成更细的粒度。将数据按照指定的字段进行分成多个桶中去，即将数据按照字段进行划分，数据按照字段划分到多个文件当中去。分桶的原理：

• MR中：key.hashCode % reductTask
• Hive中：分桶字段.hashCode % 分桶个数

测试数据

1 java 90
1 c 78
1 python 91
1 hadoop 80
2 java 75
2 c 76
2 python 80
2 hadoop 93
3 java 98
3 c 74
3 python 89
3 hadoop 91
5 java 93
6 c 76
7 python 87
8 hadoop 88

创建分桶表

create table course(
id int,
name string,
score int
)
clustered by (id) into 3 buckets
row format delimited fields terminated by "\t";
-- 创建普通表
create table course_common(
id int,
name string,
score int
)
row format delimited fields terminated by "\t";

-- 普通表加载数据
load data local inpath '/home/hadoop/data/course.dat' into
table course_common;
-- 通过 insert ... select ... 给桶表加载数据
insert into table course select * from course_common;
-- 观察分桶数据。数据按照：(分区字段.hashCode) % (分桶数) 进行分区

• 分桶规则：分桶字段.hashCode % 分桶数
• 分桶表加载数据时，使用 insert... select ... 方式进行
• 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive 1.x 以前的版本；Hive 2.x 中，删除了该参数，始终可以分桶。

修改删除表

-- 修改表名。rename
alter table course_common rename to course_common1;
-- 修改列名。change column
alter table course_common1 change column id cid int;
-- 修改字段类型。change column

alter table course_common1 change column cid cid string;
-- The following columns have types incompatible with the
existing columns in their respective positions
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是
string不能转为int
-- 增加字段。add columns
alter table course_common1 add columns (common string);
-- 删除字段：replace columns
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件
alter table course_common1 replace columns(id string, cname string, score int);
-- 删除表
drop table course_common1;